// mysql操作
const mysql = require('mysql');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'Heromy@1322',
    database: 'test',
    insecureAuth: true
});

connection.connect((err) => {
    if (err) {
        console.error('Error connecting to MySQL database: ', err);
        return;
    }
    console.log('Connected to MySQL database');

    // 创建 users 表
    const createTableQuery = `CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        age INT NOT NULL
    )`;

    connection.query(createTableQuery, (err, result) => {
        if (err) {
            console.error('Error creating users table: ', err);
            return;
        }
        console.log('Users table created successfully');

        // 添加数据到 users 表
        /*const insertDataQuery = `INSERT INTO users (name, age) VALUES ?`;
        const usersData = [
            ['John Doe', 25],
            ['Jane Smith', 30],
            ['Mike Johnson', 27],
            ['Emily Young', 28],
            ['David Lee', 24]
        ];

        connection.query(insertDataQuery, [usersData], (err, result) => {
            if (err) {
                console.error('Error inserting users data: ', err);
                return;
            }
            console.log('Users data inserted successfully');

            connection.end(); // 关闭数据库连接
        });*/

        connection.query('DELETE FROM users WHERE id = ?', 1, (err, result) => {
            if (err) {
                console.error('Error deleting user: ', err);
                return;
            }
            console.log('User deleted successfully');
        });

        // 更新数据
        /*const updateData = { name: 'Jane Doe', age: 30 };
        connection.query('UPDATE users SET ? WHERE id = ?', [updateData, 1], (err, result) => {
            if (err) {
                console.error('Error updating user: ', err);
                return;
            }
            console.log('User updated successfully');
        });*/

        // 读取数据
        connection.query('SELECT * FROM users', (err, results) => {
            if (err) {
                console.error('Error reading users: ', err);
                return;
            }
            console.log('Users:', results);
        });

        connection.end(); // 关闭数据库连接
    });
});
